SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_PWSalesDashBoard_PWCDBReporting]
	@DBName NVARCHAR(255),
	@StartDate DATETIME,
	@EndDate DATETIME
AS
BEGIN
	--DECLARE @DBName NVARCHAR(255)
	--DECLARE @StartDate DATETIME
	--DECLARE @EndDate DATETIME
	
	--SET @DBName = 'wtdev_pw'
	--SET @StartDate = '2010-01-01'
	--SET @EndDate = '2013-07-05'
	
	SET @EndDate = DATEADD(d, 1, @EndDate)

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#SalesResults') AND type in (N'U'))
		DROP TABLE #SalesResults

	CREATE TABLE #SalesResults
	(
		ReportDataId INT PRIMARY KEY IDENTITY(1,1),
		DBName NVARCHAR(500),
		PWVersion NVARCHAR(255),
		UseCDS BIT,
		ProductsTotal INT,
		AveragePricePerProductsTotal DECIMAL(18,2),
		CustomersHavePlaceOrders INT,
		CustomersHavePlaceMoreThanOneOrder INT,
		PureCDSProductsTotal INT,
		AvgPricePerPureCDSProductsTotal DECIMAL(18,2),
		CDSBundleProductsTotal INT,
		AvgPricePerCDSBundleProductsTotal DECIMAL(18,2),
		NumOfOrdersPlaced INT,
		OrdersTotal DECIMAL(18,2),
		OrdersPureTotal DECIMAL(18,2),
		LowestOrderTotal_OrderId INT,
		LowestOrderTotal_OrderTotal DECIMAL(18,2),
		LowestOrderPureTotal_OrderId INT,
		LowestOrderPureTotal_OrderPureTotal DECIMAL(18,2),
		HighestOrderTotal_OrderId INT,
		HighestOrderTotal_OrderTotal DECIMAL(18,2),
		HighestOrderPureTotal_OrderId INT,
		HighestOrderPureTotal_OrderPureTotal DECIMAL(18,2),
		OrderItemsTotal INT,
		LowestOrderItemsTotal_OrderId INT,
		LowestOrderItemsTotal_ItemsTotal INT,
		HighestOrderItemsTotal_OrderId INT,
		HighestOrderItemsTotal_OrderItemsTotal INT,
		AvgTotalPerOrder DECIMAL(18,2),
		AvgPureTotalPerOrder DECIMAL(18,2),
		AvgNumItemsPerOrder DECIMAL(18,2),
		NumOfPureCDSOrdersPlaced INT,
		PureCDSOrdersTotal DECIMAL(18,2),
		PureCDSOrdersPureTotal DECIMAL(18,2),
		LowestPureCDSOrderTotal_OrderId INT,
		LowestPureCDSOrderTotal_OrderTotal DECIMAL(18,2),
		LowestPureCDSOrderPureTotal_OrderId INT,
		LowestPureCDSOrderPureTotal_OrderPureTotal DECIMAL(18,2),
		HighestPureCDSOrderTotal_OrderId INT,
		HighestPureCDSOrderTotal_OrderTotal DECIMAL(18,2),
		HighestPureCDSOrderPureTotal_OrderId INT,
		HighestPureCDSOrderPureTotal_OrderPureTotal DECIMAL(18,2),
		PureCDSOrderItemsTotal INT,
		LowestPureCDSOrderItemsTotal_OrderId INT,
		LowestPureCDSOrderItemsTotal_ItemsTotal INT,
		HighestPureCDSOrderItemsTotal_OrderId INT,
		HighestPureCDSOrderItemsTotal_OrderItemsTotal INT,
		AvgTotalPerPureCDSOrder DECIMAL(18,2),
		AvgPureTotalPerPureCDSOrder DECIMAL(18,2),
		AvgNumItemsPerPureCDSOrder DECIMAL(18,2),
		NumOfBundleCDSOrdersPlaced INT,
		BundleCDSOrdersTotal DECIMAL(18,2),
		BundleCDSOrdersPureTotal DECIMAL(18,2),
		LowestBundleCDSOrderTotal_OrderId INT,
		LowestBundleCDSOrderTotal_OrderTotal DECIMAL(18,2),
		LowestBundleCDSOrderPureTotal_OrderId INT,
		LowestBundleCDSOrderPureTotal_OrderPureTotal DECIMAL(18,2),
		HighestBundleCDSOrderTotal_OrderId INT,
		HighestBundleCDSOrderTotal_OrderTotal DECIMAL(18,2),
		HighestBundleCDSOrderPureTotal_OrderId INT,
		HighestBundleCDSOrderPureTotal_OrderPureTotal DECIMAL(18,2),
		BundleCDSOrderItemsTotal INT,
		LowestBundleCDSOrderItemsTotal_OrderId INT,
		LowestBundleCDSOrderItemsTotal_ItemsTotal INT,
		HighestBundleCDSOrderItemsTotal_OrderId INT,
		HighestBundleCDSOrderItemsTotal_OrderItemsTotal INT,
		AvgTotalPerBundleCDSOrder DECIMAL(18,2),
		AvgPureTotalPerBundleCDSOrder DECIMAL(18,2),
		AvgNumItemsPerBundleCDSOrder DECIMAL(18,2)
	)

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#OrderInfo') AND type in (N'U'))
		DROP TABLE #OrderInfo
		
	CREATE TABLE #OrderInfo (ID INT PRIMARY KEY IDENTITY(1,1), OrderId INT, OrderItemsTotal INT, TotalOfOrder DECIMAL(18,2), NetPrice DECIMAL(18,2), OrdersPureTotal DECIMAL(18,2), OrderDate DATETIME)

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#CDSOrderInfo') AND type in (N'U'))
		DROP TABLE #CDSOrderInfo
		
	CREATE TABLE #CDSOrderInfo(ID INT PRIMARY KEY IDENTITY(1,1), OrderId INT, OrderItemId INT, CDSItemNetPrice DECIMAL(18,2), CDSItemPureTotal DECIMAL(18,2), AutoAssembly BIT)
	

	DECLARE @sql NVARCHAR(MAX)

	DECLARE @db NVARCHAR(255)

	DECLARE @UseDISC INT
	DECLARE @CurrentID INT

	SET @db = @DBName
	SET @UseDISC = 0
	SET @CurrentID = 0
	
	DECLARE @ContainsUseDISC INT
				
	SET @ContainsUseDISC = 0
	SET @sql = 'SELECT @ContainsUseDISCR = COUNT(*) FROM ' + @db + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''Settings'' AND COLUMN_NAME LIKE ''UseDISC'''
	EXECUTE sp_executesql @sql, N'@ContainsUseDISCR INT OUTPUT', @ContainsUseDISCR = @ContainsUseDISC OUTPUT
	
	IF (@ContainsUseDISC > 0)
	BEGIN
		SET @sql = 'SELECT @UseDISCR = UseDISC FROM ' + @db + '..Settings'
		EXECUTE sp_executesql @sql, N'@UseDISCR INT OUTPUT', @UseDISCR = @UseDISC OUTPUT
	END
	ELSE
	BEGIN
		SET @UseDISC = 0
	END
	
	SET @sql = '
		INSERT INTO #SalesResults(DBName, PWVersion, UseCDS)
		VALUES (@dbR, ''PWC'', @UseDISCR)
		SELECT @CurrentIDR = SCOPE_IDENTITY()'
		
	EXECUTE sp_executesql @sql, N'@CurrentIDR INT OUTPUT, @dbR NVARCHAR(255), @UseDISCR INT', @CurrentIDR = @CurrentID OUTPUT, @dbR = @db, @UseDISCR = @UseDISC
	
	IF (@CurrentID > 0)
	BEGIN
		/* ============================== GENERAL INFORMATION ================================ */
	
		/* Product Information */
		--	How many products total on the site?
		SET @sql ='UPDATE #SalesResults SET ProductsTotal = (SELECT COUNT(ProductId) FROM ' + @db + '..Product) WHERE ReportDataId = @CurrentID'
		EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
		
		-- Average price of the total products
		
		SET @sql = 'UPDATE #SalesResults SET AveragePricePerProductsTotal = (SELECT SUM(BasePrice) / COUNT(ProductId) FROM ' + @db + '..Product) WHERE ReportDataId = @CurrentID'
		EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
		/* End of Production Information */

		/* Customer Information */
		
		-- How many customers have placed orders since beginning of the site?
		SET @sql = '
			UPDATE #SalesResults
			SET CustomersHavePlaceOrders = 
			(
				SELECT COUNT(UserId) 
				FROM (
					SELECT users.UsersId AS UserId, COUNT(orders.OrdersId) AS NumberOfOrders
					FROM ' + @db + '..Users users WITH (NOLOCK) INNER JOIN ' + @db + '..Orders orders WITH (NOLOCK) ON users.UsersId = orders.UsersId
					WHERE users.Email NOT LIKE ''%cyberwolf.com''
					GROUP BY users.UsersId
				) users_orders
			)
			WHERE ReportDataId = @CurrentID'
		EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
		
		-- How many customers have placed > 1 order?
		SET @sql = '
			UPDATE #SalesResults
			SET CustomersHavePlaceMoreThanOneOrder = 
			(
				SELECT COUNT(UserId) 
				FROM (
					SELECT users.UsersId AS UserId, COUNT(orders.OrdersId) AS NumberOfOrders
					FROM ' + @db + '..Users users WITH (NOLOCK) INNER JOIN ' + @db + '..Orders orders WITH (NOLOCK) ON users.UsersId = orders.UsersId
					WHERE users.Email NOT LIKE ''%cyberwolf.com''
					GROUP BY users.UsersId
				) users_orders
				WHERE NumberOfOrders > 1
			)
			WHERE ReportDataId = @CurrentID'
		EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
		/* End Of Customer Information */
		
		/* CDS Processing */
		IF @UseDISC = 1
		BEGIN
			/* USE CDS */
			
			-- How many CDS products on the site (not bundles)?
			SET @sql = '
				UPDATE #SalesResults
				SET PureCDSProductsTotal = 
				(
					SELECT COUNT(ProductId)
					FROM ' + @db + '..Product WITH (NOLOCK)
					WHERE [FileName] <> '''' AND [FileName] <> ''0'' AND AutoAssembly <> 1
				)
				WHERE ReportDataId = @CurrentID'
			EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
			
			-- Average price of the pure CDS products (not bundles)?
			SET @sql = '
				UPDATE #SalesResults
				SET AvgPricePerPureCDSProductsTotal = 
				(
					SELECT CASE WHEN COUNT(ProductId) > 0 THEN SUM(BasePrice) / COUNT(ProductId) ELSE 0 END
					FROM ' + @db + '..Product WITH (NOLOCK)
					WHERE [FileName] <> '''' AND [FileName] <> ''0'' AND AutoAssembly <> 1
				)
				WHERE ReportDataId = @CurrentID'
			EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
			
			-- How many bundles?
			SET @sql = '
				UPDATE #SalesResults
				SET CDSBundleProductsTotal = 
				(
					SELECT COUNT(ProductId)
					FROM ' + @db + '..Product WITH (NOLOCK)
					WHERE [FileName] <> '''' AND [FileName] <> ''0'' AND AutoAssembly = 1
				)
				WHERE ReportDataId = @CurrentID'
			EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
			
			-- Average price of bundles?
			SET @sql = '
				UPDATE #SalesResults
				SET AvgPricePerCDSBundleProductsTotal = 
				(
					SELECT CASE WHEN COUNT(ProductId) > 0 THEN SUM(BasePrice) / COUNT(ProductId) ELSE 0 END
					FROM ' + @db + '..Product WITH (NOLOCK)
					WHERE [FileName] <> '''' AND [FileName] <> ''0'' AND AutoAssembly = 1
				)
				WHERE ReportDataId = @CurrentID'
			EXECUTE sp_executesql @sql, N'@CurrentID INT', @CurrentID
		END
		/* End Of CDS Processing */
		/* ============================== END OF GENERAL INFORMATION ================================ */
		
		/* ============================== ORDER INFORMATION: MUST HAVE SELECTED TIME FRAME ======================================== */
		DELETE FROM #OrderInfo --Reset this table

		SET @sql = '
			INSERT INTO #OrderInfo (OrderId, OrderItemsTotal, TotalOfOrder, NetPrice, OrdersPureTotal, OrderDate)
			SELECT 
				O.OrdersId AS OrderId, COUNT(OI.OrdersItemId) AS OrderItemsTotal, O.OrderTotal AS TotalOfOrder, 
				SUM(OI.ExtendedNetPrice + OI.ExtendedBackOrderNetPrice) AS NetPrice, 
				SUM(OI.Price * (OI.Quantity + OI.BackOrderQuantity)) AS OrdersPureTotal,
				O.DateOrdered AS OrderDate
			FROM ' + @db + '..Orders O WITH (NOLOCK) JOIN ' + @db + '..OrdersItem OI WITH (NOLOCK) ON O.OrdersId = OI.OrdersId
			WHERE
				(O.DateOrdered BETWEEN @StartDate AND @EndDate) AND 
				(O.UsersId IN (SELECT UsersId FROM ' + @db + '..Users WITH (NOLOCK) WHERE NOT Email LIKE ''%cyberwolf.com''))
			GROUP BY O.OrdersId, O.OrderTotal, O.DateOrdered'
		EXECUTE sp_executesql @sql, N'@StartDate DATETIME, @EndDate DATETIME', @StartDate, @EndDate
		
		-- How many orders were placed?
		UPDATE #SalesResults
		SET NumOfOrdersPlaced = (SELECT COUNT(OrderId) FROM #OrderInfo)
		WHERE ReportDataId = @CurrentID
		
		-- What is the $$ total of the orders?
		UPDATE #SalesResults
		SET 
			--OrdersTotal = (SELECT SUM(NetPrice) FROM #OrderInfo),
			OrdersTotal = (SELECT SUM(TotalOfOrder) FROM #OrderInfo),
			OrdersPureTotal = (SELECT SUM(OrdersPureTotal) FROM #OrderInfo)
		WHERE ReportDataId = @CurrentID
		
		-- What is the lowest and the highest $$?
		UPDATE #SalesResults
		SET
			LowestOrderTotal_OrderId = (SELECT TOP 1 OrderId FROM #OrderInfo ORDER BY NetPrice),
			LowestOrderTotal_OrderTotal = (SELECT TOP 1 NetPrice FROM #OrderInfo ORDER BY NetPrice),
			LowestOrderPureTotal_OrderId = (SELECT TOP 1 OrderId FROM #OrderInfo ORDER BY OrdersPureTotal),
			LowestOrderPureTotal_OrderPureTotal = (SELECT TOP 1 OrdersPureTotal FROM #OrderInfo ORDER BY OrdersPureTotal),
			HighestOrderTotal_OrderId = (SELECT TOP 1 OrderId FROM #OrderInfo ORDER BY NetPrice DESC),
			HighestOrderTotal_OrderTotal = (SELECT TOP 1 NetPrice FROM #OrderInfo ORDER BY NetPrice DESC),
			HighestOrderPureTotal_OrderId = (SELECT TOP 1 OrderId FROM #OrderInfo ORDER BY OrdersPureTotal DESC),
			HighestOrderPureTotal_OrderPureTotal = (SELECT TOP 1 OrdersPureTotal FROM #OrderInfo ORDER BY OrdersPureTotal DESC)
		WHERE ReportDataId = @CurrentID
		
		-- What is the item total of these orders?
		UPDATE #SalesResults
		SET OrderItemsTotal = (SELECT SUM(OrderItemsTotal) FROM #OrderInfo)
		WHERE ReportDataId = @CurrentID
		
		-- What is the lowest and the highest number of items for these orders?
		UPDATE #SalesResults
		SET
			LowestOrderItemsTotal_OrderId = (SELECT TOP 1 OrderId FROM #OrderInfo ORDER BY OrderItemsTotal),
			LowestOrderItemsTotal_ItemsTotal = (SELECT TOP 1 OrderItemsTotal FROM #OrderInfo ORDER BY OrderItemsTotal),
			HighestOrderItemsTotal_OrderId = (SELECT TOP 1 OrderId FROM #OrderInfo ORDER BY OrderItemsTotal DESC),
			HighestOrderItemsTotal_OrderItemsTotal = (SELECT TOP 1 OrderItemsTotal FROM #OrderInfo ORDER BY OrderItemsTotal DESC)
		WHERE ReportDataId = @CurrentID
			
		-- What is the average $$ per order in this time frame
		UPDATE #SalesResults
		SET 
			AvgTotalPerOrder = (SELECT CASE WHEN COUNT(OrderId) > 0 THEN SUM(NetPrice) / COUNT(OrderId) ELSE 0 END FROM #OrderInfo),
			AvgPureTotalPerOrder = (SELECT CASE WHEN COUNT(OrderId) > 0 THEN SUM(OrdersPureTotal) / COUNT(OrderId) ELSE 0 END FROM #OrderInfo)
		WHERE ReportDataId = @CurrentID
		
		-- What is the average number of items per order in this time frame
		UPDATE #SalesResults
		SET AvgNumItemsPerOrder = (SELECT CASE WHEN COUNT(OrderId) > 0 THEN (SUM(OrderItemsTotal)* 1.0) / (COUNT(OrderId) * 1.0) ELSE 0 END FROM #OrderInfo)
		WHERE ReportDataId = @CurrentID
		
		/* CDS Processing */
		IF @UseDISC = 1
		BEGIN
			/* USE CDS */
			
			DELETE FROM #CDSOrderInfo --Reset this table.
			
			SET @sql = '
				INSERT INTO #CDSOrderInfo(OrderId, OrderItemId, CDSItemNetPrice, CDSItemPureTotal, AutoAssembly)
				SELECT 
					OI.OrdersID AS OrderId, 
					OI.OrdersItemId AS OrderItemId,
					SUM(OI.ExtendedNetPrice + OI.ExtendedBackOrderNetPrice) AS CDSItemNetPrice,
					SUM(OI.Price * (OI.Quantity + OI.BackOrderQuantity)) AS CDSItemPureTotal,
					AutoAssembly = (SELECT AutoAssembly FROM ' + @db + '..Product P WITH (NOLOCK) WHERE P.SKU = OI.SKU)
				FROM ' + @db + '..OrdersItem OI WITH (NOLOCK) 
				WHERE 
					(OI.DownloadLink IS NOT NULL AND OI.DownloadLink <> ''0'') AND OI.OrdersID IN 
					(
						SELECT OrdersID FROM ' + @db + '..Orders WITH (NOLOCK)
						WHERE 
							(DateOrdered BETWEEN @StartDate AND @EndDate) AND
							(UsersId IN (SELECT UsersId FROM ' + @db + '..Users WITH (NOLOCK) WHERE NOT Email LIKE ''%cyberwolf.com''))
					)
				GROUP BY OI.OrdersID, OI.OrdersItemId, OI.SKU'
			
			EXECUTE sp_executesql @sql, N'@StartDate DATETIME, @EndDate DATETIME', @StartDate, @EndDate
			
			-- How many orders that contain pure CDS items were placed?
			UPDATE #SalesResults
			SET NumOfPureCDSOrdersPlaced = (SELECT COUNT(DISTINCT OrderId) FROM #CDSOrderInfo WHERE AutoAssembly <> 1)
			WHERE ReportDataId = @CurrentID
			
			-- What is the $$ total of the orders that contain pure CDS items?
			UPDATE #SalesResults
			SET 
				PureCDSOrdersTotal = (SELECT SUM(CDSItemNetPrice) FROM #CDSOrderInfo WHERE AutoAssembly <> 1),
				PureCDSOrdersPureTotal = (SELECT SUM(CDSItemPureTotal) FROM #CDSOrderInfo WHERE AutoAssembly <> 1)
			WHERE ReportDataId = @CurrentID
			
			-- What is the lowest and the highest $$ of the orders that contain pure CDS items?
			UPDATE #SalesResults
			SET
				LowestPureCDSOrderTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
					) pureCDSOrder ORDER BY CDSItemNetPrice
				),
				LowestPureCDSOrderTotal_OrderTotal = 
				(
					SELECT TOP 1 CDSItemNetPrice 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY CDSItemNetPrice
				),
				LowestPureCDSOrderPureTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
					) pureCDSOrder ORDER BY CDSItemPureTotal
				),
				LowestPureCDSOrderPureTotal_OrderPureTotal = 
				(
					SELECT TOP 1 CDSItemPureTotal
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY CDSItemPureTotal
				),
				HighestPureCDSOrderTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY CDSItemNetPrice DESC
				),
				HighestPureCDSOrderTotal_OrderTotal = 
				(
					SELECT TOP 1 CDSItemNetPrice 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY CDSItemNetPrice DESC
				),
				HighestPureCDSOrderPureTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
					) pureCDSOrder ORDER BY CDSItemPureTotal DESC
				),
				HighestPureCDSOrderPureTotal_OrderPureTotal = 
				(
					SELECT TOP 1 CDSItemPureTotal
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY CDSItemPureTotal DESC
				)
			WHERE ReportDataId = @CurrentID
				
			-- What is the item total of orders that contain pure CDS items?
			UPDATE #SalesResults
			SET PureCDSOrderItemsTotal = (SELECT COUNT(OrderItemId) FROM #CDSOrderInfo WHERE AutoAssembly <> 1)
			WHERE ReportDataId = @CurrentID
			
			-- What is the lowest and the highest number of items for orders that contain pure CDS items?
			UPDATE #SalesResults
			SET
				LowestPureCDSOrderItemsTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS PureCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY PureCDSOrderItemsTotal
				),
				LowestPureCDSOrderItemsTotal_ItemsTotal =
				(
					SELECT TOP 1 PureCDSOrderItemsTotal 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS PureCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY PureCDSOrderItemsTotal
				),
				HighestPureCDSOrderItemsTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS PureCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY PureCDSOrderItemsTotal DESC
				),
				HighestPureCDSOrderItemsTotal_OrderItemsTotal =
				(
					SELECT TOP 1 PureCDSOrderItemsTotal 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS PureCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly <> 1 GROUP BY OrderId
						) pureCDSOrder ORDER BY PureCDSOrderItemsTotal DESC
				)
			WHERE ReportDataId = @CurrentID
			
			-- What is the average $$ per order that contains Pure CDS Items
			UPDATE #SalesResults
			SET 
				AvgTotalPerPureCDSOrder = 
				(
					SELECT CASE WHEN COUNT(OrderId) > 0 THEN SUM(CDSItemNetPrice) / COUNT(DISTINCT OrderId) ELSE 0 END FROM #CDSOrderInfo WHERE AutoAssembly <> 1
				),
				AvgPureTotalPerPureCDSOrder = 
				(
					SELECT CASE WHEN COUNT(OrderId) > 0 THEN SUM(CDSItemPureTotal) / COUNT(DISTINCT OrderId) ELSE 0 END FROM #CDSOrderInfo WHERE AutoAssembly <> 1
				)
			WHERE ReportDataId = @CurrentID
			
			--What is the average number of items per order that cotains Pure CDS Items
			UPDATE #SalesResults
			SET AvgNumItemsPerPureCDSOrder = 
			(
				SELECT CASE WHEN COUNT(OrderId) > 0 THEN (COUNT(OrderItemId) * 1.0) / (COUNT(DISTINCT OrderId) * 1.0) ELSE 0 END FROM #CDSOrderInfo WHERE AutoAssembly <> 1
			)
			WHERE ReportDataId = @CurrentID
		
			-- How many orders that contain Bunlde CDS items were placed?
			UPDATE #SalesResults
			SET NumOfBundleCDSOrdersPlaced = (SELECT COUNT(DISTINCT OrderId) FROM #CDSOrderInfo WHERE AutoAssembly = 1)
			WHERE ReportDataId = @CurrentID
			
			-- What is the $$ total of the orders that contain Bunlde CDS items?
			UPDATE #SalesResults
			SET 
				BundleCDSOrdersTotal = (SELECT SUM(CDSItemNetPrice) FROM #CDSOrderInfo WHERE AutoAssembly = 1),
				BundleCDSOrdersPureTotal = (SELECT SUM(CDSItemPureTotal) FROM #CDSOrderInfo WHERE AutoAssembly = 1)
			WHERE ReportDataId = @CurrentID
			
			-- What is the lowest and the highest $$ of the orders that contain Bunlde CDS items?
			UPDATE #SalesResults
			SET
				LowestBundleCDSOrderTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemNetPrice
				),
				LowestBundleCDSOrderTotal_OrderTotal = 
				(
					SELECT TOP 1 CDSItemNetPrice 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemNetPrice
				),
				LowestBundleCDSOrderPureTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemPureTotal
				),
				LowestBundleCDSOrderPureTotal_OrderPureTotal =
				(
					SELECT TOP 1 CDSItemPureTotal 
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemPureTotal
				),
				HighestBundleCDSOrderTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemNetPrice DESC
				),
				HighestBundleCDSOrderTotal_OrderTotal =
				(
					SELECT TOP 1 CDSItemNetPrice 
					FROM (
							SELECT OrderId, SUM(CDSItemNetPrice) AS CDSItemNetPrice FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemNetPrice DESC
				),
				HighestBundleCDSOrderPureTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemPureTotal DESC
				),
				HighestBundleCDSOrderPureTotal_OrderPureTotal =
				(
					SELECT TOP 1 CDSItemPureTotal 
					FROM (
							SELECT OrderId, SUM(CDSItemPureTotal) AS CDSItemPureTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY CDSItemPureTotal DESC
				)
			WHERE ReportDataId = @CurrentID
				
			-- What is the item total of orders that contain Bunlde CDS items?
			UPDATE #SalesResults
			SET BundleCDSOrderItemsTotal = (SELECT COUNT(OrderItemId) FROM #CDSOrderInfo WHERE AutoAssembly = 1)
			WHERE ReportDataId = @CurrentID
			
			-- What is the lowest and the highest number of items for orders that contain Bunlde CDS items?
			UPDATE #SalesResults
			SET
				LowestBundleCDSOrderItemsTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS BundleCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY BundleCDSOrderItemsTotal
				),
				LowestBundleCDSOrderItemsTotal_ItemsTotal =
				(
					SELECT TOP 1 BundleCDSOrderItemsTotal 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS BundleCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY BundleCDSOrderItemsTotal
				),
				HighestBundleCDSOrderItemsTotal_OrderId = 
				(
					SELECT TOP 1 OrderId 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS BundleCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY BundleCDSOrderItemsTotal DESC
				),
				HighestBundleCDSOrderItemsTotal_OrderItemsTotal =
				(
					SELECT TOP 1 BundleCDSOrderItemsTotal 
					FROM (
							SELECT OrderId, COUNT(OrderItemId) AS BundleCDSOrderItemsTotal FROM #CDSOrderInfo WHERE AutoAssembly = 1 GROUP BY OrderId
						) bundleCDSOrder ORDER BY BundleCDSOrderItemsTotal DESC
				)
			WHERE ReportDataId = @CurrentID
			
			-- What is the average $$ per order that contains Bunlde CDS Items
			UPDATE #SalesResults
			SET 
				AvgTotalPerBundleCDSOrder = 
				(
					SELECT CASE WHEN COUNT(OrderId) > 0 THEN SUM(CDSItemNetPrice) / COUNT(DISTINCT OrderId) ELSE 0 END FROM #CDSOrderInfo WHERE AutoAssembly = 1
				),
				AvgPureTotalPerBundleCDSOrder =
				(
					SELECT CASE WHEN COUNT(OrderId) > 0 THEN SUM(CDSItemPureTotal) / COUNT(DISTINCT OrderId) ELSE 0 END FROM #CDSOrderInfo WHERE AutoAssembly = 1
				)
			WHERE ReportDataId = @CurrentID
			
			--What is the average number of items per order that cotains Bunlde CDS Items
			UPDATE #SalesResults
			SET AvgNumItemsPerBundleCDSOrder = 
			(
				SELECT CASE WHEN COUNT(OrderId) > 0 THEN (COUNT(OrderItemId) * 1.0) / (COUNT(DISTINCT OrderId) * 1.0) ELSE 0 END FROM #CDSOrderInfo WHERE AutoAssembly = 1
			)
			WHERE ReportDataId = @CurrentID
		END
		/* End Of CDS Processing */
		
		/* ============================== END OF ORDER INFORMATION ================================ */
	END
	
	SELECT TOP 1 * FROM #SalesResults

	DROP TABLE #SalesResults
	DROP TABLE #OrderInfo
	DROP TABLE #CDSOrderInfo
END
GO
